﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Castle.DynamicProxy;
using CNKI.TPI.Web.Base;
using CNKI.TPI.Web.Search.IBLL;
using CNKI.TPI.Web.Search.IDAO;
using CNKI.TPI.Web.Search.Model;
using System.Collections;
using System.Text.RegularExpressions;
using CNKI.TPI.Web.Admin.Model;
using CNKI.TPI.Web.Admin.IDAO;
using KNet.Data.KBaseClient;
using System.IO;

namespace CNKI.TPI.Web.Search.BLL
{
    public class SearchService : BaseService, ISearchService
    {
        IUserDBDAO userDBDAO = null;
        ICategoryDAO categoryDAO = null;
        ITopicDAO topicDao = null;
        ISingleDBPublishDAO singleDBPublishDAO = null;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="categoryDAO"></param>
        /// <param name="userDBDAO"></param>
        public SearchService(IUserDBDAO userDBDAO, ICategoryDAO categoryDAO, ITopicDAO topicDao, ISingleDBPublishDAO singleDBPublishDAO)
        {
            this.userDBDAO = this.generator.CreateInterfaceProxyWithTarget<IUserDBDAO>(userDBDAO, new IInterceptor[] { new LogInterceptor() });
            this.categoryDAO = this.generator.CreateInterfaceProxyWithTarget<ICategoryDAO>(categoryDAO, new IInterceptor[] { new LogInterceptor() });
            this.topicDao = this.generator.CreateInterfaceProxyWithTarget<ITopicDAO>(topicDao, new IInterceptor[] { new LogInterceptor() });
            this.singleDBPublishDAO = this.generator.CreateInterfaceProxyWithTarget<ISingleDBPublishDAO>(singleDBPublishDAO, new IInterceptor[] { new LogInterceptor() });
        }

        /// <summary>
        /// 获得检索条件字段
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public IList<SYS_CMS_DATABASE_PUBLISH_FIELD> GetSearchFieldList(int dbID)
        {
            return userDBDAO.GetPublishField(dbID, FieldType.Search);
        }

        /// <summary>
        /// 获得跨库查询条件字段
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public IList<DBUSFIELD> GetUnionSearchFieldList()
        {
            return userDBDAO.GetUnionPublishField();
        }
        /// <summary>
        /// 获得跨库查询条件字段
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public IList<DBUSFIELD> GetUnionFieldList(FieldType fieldtype)
        {
            return userDBDAO.GetUnionFieldList(fieldtype);
        }

        /// <summary>
        /// 获得跨库检索查询数据库
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public IList<DBCONFIG> GetUnionDBList(string classCode = null)
        {
            return userDBDAO.GetUnionDB(classCode);
        }

        /// <summary>
        /// 获得概览发布字段
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public IList<SYS_CMS_DATABASE_PUBLISH_FIELD> GetOverviewFieldList(int dbID, PermissionOption option = PermissionOption.FilteringPermissions)
        {
            return userDBDAO.GetPublishField(dbID, FieldType.Overview, option);
        }

        /// <summary>
        /// 获得细览发布字段
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public IList<SYS_CMS_DATABASE_PUBLISH_FIELD> GetDetailFieldList(int dbID)
        {
            return userDBDAO.GetPublishField(dbID, FieldType.Detail);
        }

        /// <summary>
        /// 获得排序发布字段
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public IList<SYS_CMS_DATABASE_PUBLISH_FIELD> GetSortFieldList(int dbID)
        {
            return userDBDAO.GetPublishField(dbID, FieldType.Sort);
        }

        public DataSet SingleSearch(int dbID, string dbName, IList<SearchQuery> searchQuery, int selectedCatTypeID, string categroyID, string strGroup, string strOrder, int pageIndex, int pageSize, string selectField = null, PermissionOption option = PermissionOption.FilteringPermissions, bool enableCache = true, string topicCLSCode = "", string topicCLSNodeCode = "")
        {
            string query = "";
            return SingleSearch(dbID, dbName, searchQuery, selectedCatTypeID, categroyID, strGroup, strOrder, pageIndex, pageSize, out query, selectField, option, enableCache, topicCLSCode, topicCLSNodeCode);
        }
        /// <summary>
        /// 单库检索
        /// </summary>
        /// <param name="dbID"></param>
        /// <param name="searchQuery"></param>
        /// <param name="selectedCatTypeID"></param>
        /// <param name="categroyID"></param>
        /// <returns></returns>
        public DataSet SingleSearch(int dbID, string dbName, IList<SearchQuery> searchQuery, int selectedCatTypeID, string categroyID, string strGroup, string strOrder, int pageIndex, int pageSize, out string query, string selectField = null, PermissionOption option = PermissionOption.FilteringPermissions, bool enableCache = true, string topicCLSCode = "", string topicCLSNodeCode = "")
        {
            IList<SYS_CMS_DATABASE_PUBLISH_FIELD> overviewField = new List<SYS_CMS_DATABASE_PUBLISH_FIELD>();
            SYS_CMS_DATABASE dbInfo = categoryDAO.GetDatabaseByID(dbID);
            if (string.IsNullOrEmpty(selectField))
            {
                //获得概览发布字段
                overviewField = GetOverviewFieldList(dbID, option);
                if ((overviewField == null || overviewField.Count == 0) && dbInfo.DatabaseType == (int)MediaType.News)
                {
                    overviewField = new List<SYS_CMS_DATABASE_PUBLISH_FIELD>();
                    overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "标题", FieldDispName = "标题" });
                    overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "作者", FieldDispName = "作者" });
                    overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "发布时间", FieldDispName = "发布时间" });
                    overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "发布单位", FieldDispName = "发布单位" });
                    strOrder = "发布时间 desc";
                }
                if (dbInfo.DatabaseType == 12)
                {
                    if (overviewField == null)
                    {
                        overviewField = new List<SYS_CMS_DATABASE_PUBLISH_FIELD>();
                        overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "SHOWNAME", FieldDispName = "SHOWNAME" });
                        overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "URL", FieldDispName = "URL" });
                        overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "OPENTYPE", FieldDispName = "OPENTYPE" });
                    }
                    else
                    {
                        if (overviewField.Where(c => c.FieldName.ToUpper() == "SHOWNAME").ToList().Count == 0)
                        {
                            overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "SHOWNAME", FieldDispName = "SHOWNAME" });
                        }
                        if (overviewField.Where(c => c.FieldName.ToUpper() == "URL").ToList().Count == 0)
                        {
                            overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "URL", FieldDispName = "URL" });
                        }
                        if (overviewField.Where(c => c.FieldName.ToUpper() == "OPENTYPE").ToList().Count == 0)
                        {
                            overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "OPENTYPE", FieldDispName = "OPENTYPE" });
                        }
                    }
                    strOrder = "";
                }
            }
            else
            {
                IList<SYS_CMS_DATABASE_PUBLISH_FIELD> dbOverviewField = GetOverviewFieldList(dbID, option);
                //overviewField = new List<SYS_CMS_DATABASE_PUBLISH_FIELD>();
                string[] fieldList = selectField.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string item in fieldList)
                {
                    IList<SYS_CMS_DATABASE_PUBLISH_FIELD> dbField = dbOverviewField.Where(c => c.FieldName == item).ToList();
                    if (dbField != null && dbField.Count > 0)
                    {
                        overviewField.Add(dbField[0]);
                    }
                    else
                    {
                        overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = item, FieldDispName = item });
                    }
                }
            }
            string select = BuildSelect(overviewField, dbInfo);
            //生成检索条件
            string where = BuildWhere(dbName, searchQuery, selectedCatTypeID, categroyID, out query, topicCLSCode, topicCLSNodeCode);
            //检索
            DataSet ds = userDBDAO.SingleSearch(dbID, select, where, strGroup, strOrder, pageIndex, pageSize, option, enableCache);
            //处理结果集

            ProcessResult(ds, overviewField, dbInfo);

            return ds;
        }

        private string BuildSelect(IList<SYS_CMS_DATABASE_PUBLISH_FIELD> overviewField, SYS_CMS_DATABASE dbInfo)
        {
            string select = "";
            switch ((SearchType)dbInfo.SearchType)
            {
                case SearchType.TPI:
                    overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "SYS_FLD_SYSID" });
                    select = string.Join(",", overviewField.Select(x => x.FieldName));
                    break;
                case SearchType.CNKI:
                    IList<string> fieldList1 = overviewField.Select(x => x.FieldName).ToList();
                    if (!fieldList1.Contains(ConfigHelper.GetCNKIResourceKey()))
                    {
                        fieldList1.Add(ConfigHelper.GetCNKIResourceKey());
                    }
                    select = string.Join(",", fieldList1);
                    break;
                case SearchType.KSPIRDER:
                    IList<string> fieldList2 = overviewField.Select(x => x.FieldName).ToList();
                    if (!fieldList2.Contains(ConfigHelper.GetKspiderKey()))
                    {
                        fieldList2.Add(ConfigHelper.GetKspiderKey());
                    }
                    select = string.Join(",", fieldList2);
                    break;
                default:
                    overviewField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "SYS_FLD_SYSID" });
                    select = string.Join(",", overviewField.Select(x => x.FieldName));
                    break;
            }
            return select;
        }

        /// <summary>
        /// 多库检索
        /// </summary>
        /// <param name="dbID"></param>
        /// <param name="searchQuery"></param>
        /// <param name="selectedCatTypeID"></param>
        /// <param name="categroyID"></param>
        /// <returns></returns>
        public DataSet MultiSearch(string dbID, string dbName, IList<SearchQuery> searchQuery, int selectedCatTypeID, string categroyID, string strGroup, string strOrder, int pageIndex, int pageSize, string classCode)
        {
            string query = "";
            return MultiSearch(dbID, dbName, searchQuery, selectedCatTypeID, categroyID, strGroup, strOrder, pageIndex, pageSize, classCode, out query);
        }
        /// <summary>
        /// 多库检索
        /// </summary>
        /// <param name="dbID"></param>
        /// <param name="searchQuery"></param>
        /// <param name="selectedCatTypeID"></param>
        /// <param name="categroyID"></param>
        /// <returns></returns>
        public DataSet MultiSearch(string dbID, string dbName, IList<SearchQuery> searchQuery, int selectedCatTypeID, string categroyID, string strGroup, string strOrder, int pageIndex, int pageSize, string classCode, out string str_query)
        {
            //获得概览发布字段
            //IList<string> overviewField = GetUnionOverviewFieldList();
            IList<DBUSFIELD> overviewField = GetUnionFieldList(FieldType.Overview);
            IList<SYS_CMS_DATABASE> dbList = null;
            if (dbID.Contains(','))
            {
                dbList = categoryDAO.GetDatabaseByID(dbID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList());
            }
            else
            {
                dbList = categoryDAO.GetDatabaseByID(new List<int>() { int.Parse(dbID) });
            }
            int m = 0;
            foreach (DBUSFIELD item in overviewField)
            {
                if (item.RealFieldName == "SYS_FLD_SYSID")
                {
                    m = 1;
                    break;
                }
            }

            string select = string.Join(",", overviewField.Select(x => x.RealFieldName));
            if ((!string.IsNullOrEmpty(select)) && m == 0)
            {
                select = select + ",SYS_FLD_SYSID";
            }
            //生成检索条件
            string where = BuildWhere(dbName, searchQuery, selectedCatTypeID, categroyID, out str_query);
            //检索
            DataSet ds = userDBDAO.MultiSearch(dbList, select, where, strGroup, strOrder, pageIndex, pageSize, classCode);
            //处理结果集
            ProcessResult(ds, overviewField, dbList);
            return ds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public DataSet JumpInfoSearch(int dbID)
        {
            string select = "ShowName,Url,OpenType,SYS_FLD_SYSID";
            //生成检索条件
            //string where = BuildWhere(dbName, searchQuery, 0, "");
            //检索
            DataSet ds = userDBDAO.SingleSearch(dbID, select, "", "", "SORTNMU", 1, 100);
            //处理结果集
            //ProcessResult(ds, overviewField, 30);

            return ds;
        }

        /// <summary>
        /// 细缆处理结果集
        /// </summary>
        /// <param name="ds"></param>
        private void ProcessResult(DataSet ds, IList<SYS_CMS_DATABASE_PUBLISH_FIELD> overviewField)
        {
            if (ds.Tables.Count > 0)
            {
                ds.Tables[0].Columns.Remove("__sys_from");
                ds.Tables[0].Columns.Remove("__sys_link");
                ds.Tables[0].Columns.Remove("__sys_type");
                ds.Tables[0].Columns.Remove("__sys_weight");
                ds.Tables[0].Columns.Remove("__sys_name");
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        foreach (DataColumn dc in ds.Tables[0].Columns)
                        {
                            if (!dc.ColumnName.Contains("SYS_FLD_"))
                            {
                                string item = dr[dc.ColumnName].ToString();
                                item = ColumnAliasHelper.ChangeStr(item);
                                item = StringHelper.DealInfo(item);
                                //命中词汇标红
                                if ((!String.IsNullOrEmpty(item)) && item.Contains("###") && item.Contains("$$$"))
                                {
                                    item = item.Replace("###", "<span style=\"color:red;margin: 0px\">");
                                    item = item.Replace("$$$", "</span>");
                                    //isChange = true;
                                }
                                if (!String.IsNullOrEmpty(item))
                                    dr[dc.ColumnName] = item;
                            }
                        }
                    }
                }
            }
            ds.Tables.Add(BuildColumnInfo(overviewField));
        }

        /// <summary>
        /// 单库检索处理结果集
        /// </summary>
        /// <param name="ds"></param>
        private void ProcessResult(DataSet ds, IList<SYS_CMS_DATABASE_PUBLISH_FIELD> overviewField, SYS_CMS_DATABASE dbInfo)
        {
            if (ds.Tables.Count > 0)
            {
                ds.Tables[0].Columns.Remove("__sys_from");
                ds.Tables[0].Columns.Remove("__sys_link");
                ds.Tables[0].Columns.Remove("__sys_type");
                ds.Tables[0].Columns.Remove("__sys_weight");
                ds.Tables[0].Columns.Remove("__sys_name");
                ds.Tables[0].Columns.Add("SYS_FLD_URL");
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        foreach (DataColumn dc in ds.Tables[0].Columns)
                        {
                            if (!dc.ColumnName.Contains("SYS_FLD_"))
                            {

                                string item = dr[dc.ColumnName].ToString();
                                item = ColumnAliasHelper.ChangeStr(item);
                                item = StringHelper.DealInfo(item);
                                //命中词汇标红
                                if ((!String.IsNullOrEmpty(item)) && item.Contains("###") && item.Contains("$$$"))
                                {
                                    item = item.Replace("###", "<span style=\"color:red;margin: 0px\">");
                                    item = item.Replace("$$$", "</span>");
                                    //isChange = true;
                                }
                                if (!String.IsNullOrEmpty(item))
                                    dr[dc.ColumnName] = item;
                            }
                        }
                        dr["SYS_FLD_URL"] = ResourceHelper.GetSingleSearchDetailURL(dbInfo, dr);
                    }
                }

            }
            ds.Tables.Add(BuildColumnInfo(overviewField));
        }

        /// <summary>
        /// 跨库处理结果集
        /// </summary>
        /// <param name="ds"></param>
        private void ProcessResult(DataSet ds, IList<DBUSFIELD> overviewField, IList<SYS_CMS_DATABASE> dbList)
        {
            Hashtable dbInfo = new Hashtable();
            if (ds.Tables.Count > 0)
            {

                //ds.Tables[0].Columns.Remove("__sys_from"); //存放数据库Id，code,名称，类型。跨库时列表需要该值，不能移除
                ds.Tables[0].Columns.Remove("__sys_link");
                ds.Tables[0].Columns.Remove("__sys_type");
                ds.Tables[0].Columns.Remove("__sys_weight");
                ds.Tables[0].Columns.Remove("__sys_name");
                ds.Tables[0].Columns.Add("SYS_FLD_URL");
                foreach (DataRow dr in ds.Tables[1].Rows)
                {
                    dbInfo.Add(dr["code"], dr["name"]);
                }

                //bool isChange = false;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    foreach (DataColumn dc in ds.Tables[0].Columns)
                    {
                        if ((!dc.ColumnName.Contains("SYS_FLD_")) || (!dc.ColumnName.Contains("__sys_")))
                        {
                            string item = dr[dc.ColumnName].ToString();
                            item = ColumnAliasHelper.ChangeStr(item);
                            item = StringHelper.DealInfo(item);
                            //命中词汇标红
                            if ((!String.IsNullOrEmpty(item)) && item.Contains("###") && item.Contains("$$$"))
                            {
                                item = item.Replace("###", "<span style=\"color:red;margin: 0px\">");
                                item = item.Replace("$$$", "</span>");
                                //isChange = true;
                            }
                            if (!String.IsNullOrEmpty(item))
                                dr[dc.ColumnName] = item;
                        }

                    }
                    int dbid = int.Parse(dr["__sys_from"].ToString().Split('#')[0].ToString());

                    dr["SYS_FLD_URL"] = ResourceHelper.GetMultiSearchDetailURL(dbList.Where(c => c.SysID == dbid).ToList()[0], dr);
                }
            }
            ds.Tables.Add(UnionColumnInfo(overviewField));//概览显示字段
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="overviewField"></param>
        /// <returns></returns>
        private DataTable BuildColumnInfo(IList<SYS_CMS_DATABASE_PUBLISH_FIELD> overviewField)
        {
            DataTable dt = new DataTable("ColumnInfo");
            dt.Columns.Add("Name");
            dt.Columns.Add("DispName");
            dt.Columns.Add("Width");
            foreach (SYS_CMS_DATABASE_PUBLISH_FIELD item in overviewField)
            {
                dt.Rows.Add(new object[] { item.FieldName, item.FieldDispName, item.BriefWidth });
            }
            return dt;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="overviewField"></param>
        /// <returns></returns>
        private DataTable UnionColumnInfo(IList<DBUSFIELD> overviewField)
        {
            DataTable dt = new DataTable("ColumnInfo");
            dt.Columns.Add("Name");
            dt.Columns.Add("DispName");
            dt.Columns.Add("Width");
            foreach (DBUSFIELD item in overviewField)
            {
                dt.Rows.Add(new object[] { item.RealFieldName, item.ShowFieldName, item.Width });
            }
            return dt;
        }

        private string BuildWhere(string dbName, IList<SearchQuery> searchQuery, int selectedCatTypeID, string categroyID, string topicCLSCode = "", string topicCLSNodeCode = "")
        {
            string str_query = "";
            return BuildWhere(dbName, searchQuery, selectedCatTypeID, categroyID, out str_query, topicCLSCode, topicCLSNodeCode);
        }
        /// <summary>
        /// 生成检索条件
        /// </summary>
        /// <param name="searchQuery"></param>
        /// <param name="categroyID"></param>
        /// <returns></returns>
        private string BuildWhere(string dbName, IList<SearchQuery> searchQuery, int selectedCatTypeID, string categroyID, out string str_query, string topicCLSCode = "", string topicCLSNodeCode = "")
        {
            string where = "";
            string checkwhere = "";//审核条件
            if (searchQuery != null)
            {
                IList<SYS_CMS_DATABASE_FIELD> fieldList = singleDBPublishDAO.GetDBField(dbName);
                IList<SYS_CMS_DATABASE_FIELD> selectFieldList = null;
                SYS_CMS_DATABASE_FIELD selectFieldInfo = null;
                foreach (SearchQuery item in searchQuery)
                {
                    string formatstr = " {0} ({1} {3} '{2}') ";
                    if (item.Key.IndexOf("SYS_FLD_CHECKSTATE") >= 0) //审核查询条件不能加‘’SYS_FLD_CHECKSTATE=(1+2+0）
                    {
                        formatstr = " {0} ({1} {3} {2}) ";
                        checkwhere = string.IsNullOrEmpty(item.Text.Trim()) ? "" : (string.Format(formatstr, item.Type, item.Key, item.Text.Trim(), item.ConditionType));
                    }
                    else
                    {
                        selectFieldList = fieldList.Where(c => c.Field.ToLower() == item.Key.ToLower()).ToList();
                        selectFieldInfo = null;
                        if (selectFieldList != null && selectFieldList.Count > 0)
                        {
                            selectFieldInfo = selectFieldList[0];
                        }
                        if (selectFieldInfo != null && ((FieldIndexType)selectFieldInfo.IndexFLDType).ToString().Contains("CHAR") && item.ConditionType == ConditionType.Like)
                        {
                            where += string.IsNullOrEmpty(item.Text.Trim()) ? "" : (string.Format(formatstr, item.Type, item.Key, "?" + item.Text.Trim(), ConditionType.Eq));
                        }
                        else
                        {
                            where += string.IsNullOrEmpty(item.Text.Trim()) ? "" : (string.Format(formatstr, item.Type, item.Key, item.Text.Trim(), item.ConditionType));
                        }
                    }
                }
                if (!string.IsNullOrEmpty(where))
                {
                    where = checkwhere + where.Substring(0, 4) + "(" + where.Substring(4, where.Length - 4) + ")";
                }
                else
                {
                    where = checkwhere;
                }
                //where = string.Join("", searchQuery.Select(x =>
                //    string.IsNullOrEmpty(x.Text.Trim()) ? "" : (string.Format("{0} ({1} {3} '{2}') ", x.Type, x.Key, x.Text.Trim(), x.ConditionType))));
            }
            if (string.IsNullOrEmpty(categroyID))
            {
                where = string.IsNullOrEmpty(where) ? "" : where.Substring(4, where.Length - 4);
            }
            else
            {
                CLS categoryType = categoryDAO.GetCategroyTypeBySysID(dbName, selectedCatTypeID);
                //categroyID = processCategoryID(dbName, selectedCatTypeID, categroyID);
                categroyID = processCategoryID(categroyID);
                categroyID = categroyID.Replace(",", "') Or (" + categoryType.Expression + " Eq '");
                categroyID = "(" + categoryType.Expression + " Eq '" + categroyID + "')";
                //categroyID = categroyID.Substring(4, categroyID.Length - 4);
                if (string.IsNullOrEmpty(where))
                {
                    where = string.Format("({0})", categroyID);
                }
                else
                {
                    string newwhere = where.Substring(4, where.Length - 4);
                    where = string.Format("({0}) {1} ({2})", categroyID, "And", newwhere);
                }
            }
            str_query = where;
            if ((!string.IsNullOrEmpty(topicCLSCode)) && (!string.IsNullOrEmpty(topicCLSNodeCode)))
            {
                SYS_CMS_TOPIC_CLS_QUERY query = topicDao.GetDBTopicQuery(dbName, topicCLSCode, topicCLSNodeCode);
                if (query != null && !string.IsNullOrEmpty(query.Query))
                {
                    if (string.IsNullOrEmpty(where.Trim()))
                    {
                        where = query.Query;
                    }
                    else
                    {
                        where = where + " And (" + query.Query + ")";
                    }
                }
            }
            return where;
        }

        /// <summary>
        /// 去除子节点
        /// </summary>
        /// <returns></returns>
        public string processCategoryID(string categoryid)
        {
            string result = "";
            try
            {
                IList<string> categoryArray = categoryid.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                string currentCategoryid = null;
                for (int i = 0; i < categoryArray.Count; i++)
                {
                    currentCategoryid = categoryArray[i];
                    categoryArray = categoryArray.Where(c => !((c.StartsWith(currentCategoryid)) && (!c.Equals(currentCategoryid)))).ToList();
                    categoryArray[i] = categoryArray[i] + "?";
                }
                result = string.Join(",", categoryArray);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        /// <summary>
        /// 去除子节点
        /// </summary>
        /// <param name="dbCode"></param>
        /// <param name="catTypeID"></param>
        /// <param name="categoryid"></param>
        /// <returns></returns>
        /// 
        //public string processCategoryID(string dbCode, int catTypeID, string categoryid)
        //{
        //    IList<CLS_NODE> nodeList = categoryDAO.GetCategroyListByCode(dbCode, catTypeID, categoryid);
        //    string catSysCodeList = CompressCategoryCode(string.Join(",", nodeList.Select(c => c.SysCode)));
        //    nodeList = categoryDAO.GetCategroyListBySysCode(dbCode, catTypeID, catSysCodeList);
        //    return string.Join(",", nodeList.Select(c => c.Code + "?"));

        //}
        /// <summary>
        /// 去除子节点
        /// </summary>
        /// <returns></returns>
        //private string CompressCategoryCode(string categoryid)
        //{
        //    string result = "";
        //    try
        //    {
        //        IList<string> categoryArray = categoryid.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
        //        string currentCategoryid = null;
        //        for (int i = 0; i < categoryArray.Count; i++)
        //        {
        //            currentCategoryid = categoryArray[i];
        //            categoryArray = categoryArray.Where(c => !((c.StartsWith(currentCategoryid)) && (!c.Equals(currentCategoryid)))).ToList();
        //        }
        //        result = string.Join(",", categoryArray);
        //    }
        //    catch (Exception ex)
        //    {
        //        LogOpr.Error(ex.ToString());
        //    }
        //    return result;
        //}



        /// <summary>
        /// 加载细览
        /// </summary>
        /// <param name="dbID"></param>
        /// <param name="dbName"></param>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public DataSet SearchDetail(int dbID, IList<SearchQuery> searchQuery)
        {
            DataSet result = new DataSet();
            SYS_CMS_DATABASE dbInfo = categoryDAO.GetDatabaseByID(dbID);
            if (dbInfo != null)
            {
                result = SearchDetail(dbID, dbInfo.DatabaseCode, searchQuery, dbInfo);
            }
            return result;
        }

        /// <summary>
        /// 加载细览
        /// </summary>
        /// <param name="dbID"></param>
        /// <param name="dbName"></param>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public DataSet SearchDetail(int dbID, string dbName, IList<SearchQuery> searchQuery, SYS_CMS_DATABASE dbInfo)
        {
            //获得细览发布字段
            IList<SYS_CMS_DATABASE_PUBLISH_FIELD> detailField = GetDetailFieldList(dbID);

            if (detailField.Count == 0 && dbInfo.DatabaseType == (int)MediaType.News)
            {
                detailField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "标题", FieldDispName = "标题" });
                detailField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "作者", FieldDispName = "作者" });
                detailField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "发布时间", FieldDispName = "发布时间" });
                detailField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "发布单位", FieldDispName = "发布单位" });
                detailField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "全文", FieldDispName = "全文" });
            }

            string existfields = string.Join(",", detailField.Select(x => x.FieldName));
            if (!existfields.Contains("SYS_FLD_VSM"))
            {
                detailField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "SYS_FLD_VSM" });
            }
            if (!existfields.Contains("SYS_FLD_DOWNLOADRATE"))
            {
                detailField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "SYS_FLD_DOWNLOADRATE" });
            }
            if (!existfields.Contains("SYS_FLD_BROWSERATE"))
            {
                detailField.Add(new SYS_CMS_DATABASE_PUBLISH_FIELD { FieldName = "SYS_FLD_BROWSERATE" });
            }
            string select = string.Join(",", detailField.Select(x => x.FieldName));
            //生成检索条件
            string where = BuildWhere(dbName, searchQuery, 0, "");
            //检索
            DataSet ds = userDBDAO.SingleSearch(dbID, select, where, "", "", 1, 10);
            //处理结果集
            ProcessResult(ds, detailField);
            return ds;
        }

        /// <summary>
        /// 获取文件
        /// </summary>
        /// <param name="recordID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public IList<METADATA_FILE> GetFile(int recordID, string dbName)
        {
            return userDBDAO.GetFile(recordID, dbName);
        }
        /// <summary>
        /// 获取文件信息,过滤文件类型
        /// </summary>
        /// <param name="recordID"></param>
        /// <param name="dbName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public IList<METADATA_FILE> GetFile(int recordID, string dbName, MediaType type)
        {
            IList<METADATA_FILE> listfile = userDBDAO.GetFile(recordID, dbName);
            IList<METADATA_FILE> listfileNew = new List<METADATA_FILE>();
            if (listfile != null)
            {

                if (type == MediaType.Audio)
                {
                    foreach (METADATA_FILE m in listfile)
                    {
                        if (FileService.CheckAudio(m.FORMAT))
                        {
                            listfileNew.Add(m);
                        }
                    }
                }
                else if (type == MediaType.Video)
                {
                    foreach (METADATA_FILE m in listfile)
                    {
                        if (FileService.CheckVideo(m.FORMAT))
                        {
                            listfileNew.Add(m);
                        }
                    }
                }
                else if (type == MediaType.Doc || type == MediaType.Ebook)
                {
                    foreach (METADATA_FILE m in listfile)
                    {
                        if (FileService.CheckDoc(m.FORMAT))
                        {
                            listfileNew.Add(m);
                        }
                    }
                }
                else if (type == MediaType.Pic || type == MediaType.News)
                {
                    foreach (METADATA_FILE m in listfile)
                    {
                        if (FileService.CheckPic(m.FORMAT))
                        {
                            listfileNew.Add(m);
                        }
                    }
                }

            }

            return listfileNew;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbId"></param>
        /// <returns></returns>
        public DataTable GetSimilarList(string dbCode)
        {
            DataTable result = InitSimilarTable();
            DataRow dr = null;
            try
            {
                IList<SYS_CMS_DATABASE_RELATION> similarList = userDBDAO.GetSimilarList(dbCode);
                IList<SYS_CMS_DATABASE> dbList = null;
                if (similarList != null && similarList.Count > 0)
                {
                    dbList = categoryDAO.GetDatabaseByCode(similarList.Select(c => c.RelationDBCode));
                }
                if (dbList != null && dbList.Count > 0)
                {
                    foreach (SYS_CMS_DATABASE db in dbList)
                    {
                        dr = result.NewRow();
                        dr["SysID"] = db.SysID;
                        dr["DatabaseCode"] = db.DatabaseCode;
                        dr["DatabaseName"] = similarList.Where(c => c.RelationDBCode == db.DatabaseCode).ToList()[0].Title;
                        dr["TemplateID"] = db.TemplateID;
                        dr["RelationField"] = similarList.Where(c => c.RelationDBCode == db.DatabaseCode).ToList()[0].RelationDBField;
                        dr["DBField"] = similarList.Where(c => c.RelationDBCode == db.DatabaseCode).ToList()[0].DBField;
                        dr["SearchType"] = db.SearchType;
                        result.Rows.Add(dr);
                    }
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        private DataTable InitSimilarTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("SysID", typeof(int));
            dt.Columns.Add("DatabaseCode", typeof(string));
            dt.Columns.Add("DatabaseName", typeof(string));
            dt.Columns.Add("TemplateID", typeof(int));
            dt.Columns.Add("RelationField", typeof(string));
            dt.Columns.Add("DBField", typeof(string));
            dt.Columns.Add("SearchType", typeof(int));
            return dt;
        }

        /// <summary>
        /// 获得首页推送数据库
        /// </summary>
        /// <returns></returns>
        public IList<string> GetHomeBase(string typename)
        {
            IList<string> result = new List<string>();
            result = GetHomeBaseConfig(typename);
            return result;
        }

        /// <summary>
        /// 从配置文件取数据库
        /// </summary>
        /// <param name="typename"></param>
        /// <returns></returns>
        private IList<string> GetHomeBaseConfig(string typename)
        {
            IList<string> result = new List<string>();
            string basestr = ConfigHelper.GetAppSettingsValue(typename);
            if (basestr != "")
            {
                result = basestr.Split('#');
            }
            return result;
        }

        /// <summary>
        /// 根据数据库ID获取数据库实例
        /// </summary>
        /// <param name="dbID"></param>
        /// <returns></returns>
        public SYS_CMS_DATABASE GetDataByID(int dbID)
        {
            SYS_CMS_DATABASE obj = new SYS_CMS_DATABASE();
            SYS_CMS_DATABASE databaseList = categoryDAO.GetDatabaseByID(dbID);
            if (databaseList != null)
            {
                obj = databaseList;
            }
            return obj;
        }


        /// <summary>
        /// 获得跨库检索分类
        /// </summary>
        /// <returns></returns>
        public IList<DBCLASS> GetUnionClass()
        {
            return userDBDAO.GetUnionClass();
        }
        /// <summary>
        /// 根据用户ID和时间段获取检索词
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="startdate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public string GetQueryByUserCode(string userCode)
        {
            return userDBDAO.GetQueryByUserCode(userCode);
        }
        /// <summary>
        /// 删除METADATA 记录
        /// </summary>
        /// <param name="sysid"></param>
        /// <param name="dbCode"></param>
        /// <returns></returns>
        public bool DelMetaData(int sysid, string dbCode)
        {
            return userDBDAO.DelMetaData(sysid, dbCode);
        }

        /// <summary>
        /// 更新METADATA 记录
        /// </summary>
        /// <param name="sysid"></param>
        /// <param name="dbCode"></param>
        /// <returns></returns>
        public bool UpdateMetaData(string sysid, string dbCode, Dictionary<string, object> fieldList)
        {
            bool result = false;
            SYS_CMS_DATABASE dbInfo = categoryDAO.GetDatabaseByCode(dbCode);
            if (dbInfo != null)
            {
                result = userDBDAO.UpdateMetaData(sysid, dbCode, fieldList, dbInfo.IsUnicode == 1 ? true : false);
            }
            return result;
        }

        /// <summary>
        /// 插入METADATA 记录
        /// </summary>
        /// <param name="sysid"></param>
        /// <param name="dbCode"></param>
        /// <returns></returns>
        public int InsertMetaData(string dbCode, Dictionary<string, object> fieldList)
        {
            int result = 0;
            SYS_CMS_DATABASE dbInfo = categoryDAO.GetDatabaseByCode(dbCode);
            if (dbInfo != null)
            {
                result = userDBDAO.InsertMetaData(dbCode, fieldList, dbInfo.IsUnicode == 1 ? true : false);
            }
            return result;
        }

        public DataTable GetDataBySql(string sql)
        {
            DataTable dt = null;
            try
            {
                if (sql.ToUpper().StartsWith("SELECT"))
                {
                    IList<string> tableList = ParserQuerySql(sql);
                    IList<SYS_CMS_DATABASE> selectDbList = new List<SYS_CMS_DATABASE>();
                    if (CheckDBList(tableList, selectDbList))
                    {
                        dt = userDBDAO.GetDataTableBySql(sql);
                    }
                }

            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return dt;
        }

        public DataTable GetDataBySql(string sql, int pageIndex, int pageSize, out int count)
        {
            DataTable dt = null;
            count = 0;
            try
            {
                if (sql.ToUpper().StartsWith("SELECT"))
                {
                    IList<string> tableList = ParserQuerySql(sql);
                    IList<SYS_CMS_DATABASE> selectDbList = new List<SYS_CMS_DATABASE>();
                    if (CheckDBList(tableList, selectDbList))
                    {
                        dt = userDBDAO.GetDataTableBySql(sql, pageIndex, pageSize, out count, selectDbList);
                    }
                }

            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return dt;
        }

        private bool CheckDBList(IList<string> tableList, IList<SYS_CMS_DATABASE> selectDbList)
        {
            bool result = true;
            try
            {
                IList<SYS_CMS_DATABASE> dbList = userDBDAO.GetDatabaseList();
                if (dbList == null || dbList.Count == 0)
                {
                    result = false;
                }
                else
                {
                    IEnumerable<string> dbcodeList = dbList.Select(c => c.SearchType == (int)SearchType.TPI ? c.DatabaseCode.ToUpper() + "_" : c.DatabaseCode.ToUpper());
                    bool flag = false;
                    int index = 0;
                    foreach (string item in tableList)
                    {
                        flag = false;
                        index = -1;
                        foreach (string sysItem in dbcodeList)
                        {
                            index++;
                            if (item.StartsWith(sysItem))
                            {
                                selectDbList.Add(dbList[index]);
                                flag = true;
                                break;
                            }
                        }
                        if (!flag)
                        {
                            result = false;
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        private List<string> ParserQuerySql(string sql)
        {
            List<string> result = new List<string>();
            try
            {
                MatchCollection mc;
                Regex r = new Regex("(?<=FROM ).+?(?=(WHERE|FILTER BY|GROUP BY|ORDER BY|$))"); //定义一个Regex对象实例
                mc = r.Matches(sql.ToUpper());
                string item = null;
                for (int i = 0; i < mc.Count; i++) //在输入字符串中找到所有匹配
                {
                    item = mc[i].Value.Trim();
                    if (item.Contains(","))
                    {
                        result.AddRange(item.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
                    }
                    else
                    {
                        result.Add(item);
                    }
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        public int ExecuteNonQuery(string sql)
        {
            int result = 0;
            try
            {
                if (sql.ToUpper().StartsWith("UPDATE ") || sql.ToUpper().StartsWith("INSERT INTO") || sql.ToUpper().StartsWith("DELETE FROM "))
                {
                    IList<string> tableList = ParserNonQuerySql(sql);
                    IList<SYS_CMS_DATABASE> selectDbList = new List<SYS_CMS_DATABASE>();
                    if (CheckDBList(tableList, selectDbList))
                    {
                        result = userDBDAO.ExecSql(sql, false);
                    }
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        private List<string> ParserNonQuerySql(string sql)
        {
            List<string> result = new List<string>();
            try
            {
                MatchCollection mc;
                Regex r = new Regex("(?<=(UPDATE |INSERT INTO |DELETE FROM )).+?(?=(SET|WHERE|\\(|$))"); //定义一个Regex对象实例
                mc = r.Matches(sql.ToUpper());
                string item = null;
                for (int i = 0; i < mc.Count; i++) //在输入字符串中找到所有匹配
                {
                    item = mc[i].Value.Trim();
                    if (item.Contains(","))
                    {
                        result.AddRange(item.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
                    }
                    else
                    {
                        result.Add(item);
                    }
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbCode"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public Dictionary<string, string> GetFieldMatchTypeList(string dbCode, string fieldName)
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            try
            {
                SYS_CMS_DATABASE_FIELD fieldInfo = singleDBPublishDAO.GetDBField(dbCode, fieldName);
                if (fieldInfo != null && ((FieldIndexType)fieldInfo.IndexFLDType).ToString().Contains("INT"))
                {
                    result.Add("等于", "0");
                    result.Add("大于", "3");
                    result.Add("大于等于", "4");
                    result.Add("小于", "5");
                    result.Add("小于等于", "6");
                }
                else
                {
                    result.Add("模糊", "2");
                    result.Add("精确", "0");
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
    }
}
